﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace org.maquech.CEeL.Core.EducationalProgram {
    class Subject : PersistentObject {
        #region Fields and Properties

        /// <summary>
        /// The Subject's name field.
        /// </summary>
        private string name;

        /// <summary>
        /// The Subject's name.
        /// </summary>
        public string Name {
            get {
                return this.name;
            }
            set {
                if ( this.name != value )
                    modifiedDBFields.Add( "name" );
                this.name = value;
            }
        }

        /// <summary>
        /// The Subject's name abbreviation field.
        /// </summary>
        private string abbreviation;

        /// <summary>
        /// The Subject's name abbreviation.
        /// </summary>
        public string Abbreviation {
            get {
                return this.abbreviation;
            }
            set {
                if ( this.abbreviation != value )
                    modifiedDBFields.Add( "abbreviation" );
                this.abbreviation = value;
            }
        }

        /// <summary>
        /// The Subject's code field
        /// </summary>
        private string code;

        /// <summary>
        /// The Subject's code.
        /// </summary>
        public string Code {
            get {
                return this.code;
            }
            set {
                if ( this.code != value )
                    modifiedDBFields.Add( "code" );
                this.code = value;
            }
        }

        /// <summary>
        /// The Subject's suggested term number field.
        /// </summary>
        private int termNumber;

        /// <summary>
        /// The Subject's suggested term number value.
        /// </summary>
        public int TermNumber {
            get {
                return this.termNumber;
            }
            set {
                if ( this.termNumber != value )
                    modifiedDBFields.Add( "term_number" );
                this.termNumber = value;
            }
        }

        /// <summary>
        /// The Subject's credits field.
        /// </summary>
        private int credits;

        /// <summary>
        /// The Subject's credits value.
        /// </summary>
        public int Credits {
            get {
                return this.credits;
            }
            set {
                if ( this.credits != value )
                    modifiedDBFields.Add( "credits" );
                this.credits = value;
            }
        }

        /// <summary>
        /// The Subject's description field.
        /// </summary>
        private string description;

        /// <summary>
        /// The Subject's description value.
        /// </summary>
        public string Description {
            get {
                return this.description;
            }
            set {
                if ( this.description != value )
                    modifiedDBFields.Add( "description" );
                this.description = value;
            }
        }

        /// <summary>
        /// The Subject's required field.
        /// </summary>
        private bool required;

        /// <summary>
        /// True if <code>this</code> is required, false otherwise.
        /// </summary>
        public bool Required {
            get {
                return this.required;
            }
            set {
                if ( this.required != value )
                    modifiedDBFields.Add( "required" );
                this.required = value;
            }
        }

        /// <summary>
        /// The passingGrade field used to divide the educational plan's total time.
        /// </summary>
        private Grade passingGrade;

        /// <summary>
        /// The passingGrade value used to divide the educational plan's total time.
        /// </summary>
        public Grade PassingGrade {
            get {
                return this.passingGrade;
            }
            set {
                if ( !this.passingGrade.Equals( value ) )
                    modifiedDBFields.Add( "passing_grade_id" );
                this.passingGrade = value;
            }
        }

        /// <summary>
        /// The plan's educational level field.
        /// </summary>
        private EducationalPlan educationalPlan;

        /// <summary>
        /// The education level associated to the educational plan.
        /// </summary>
        public EducationalPlan EducationalPlan {
            get {
                return this.educationalPlan;
            }
            set {
                if ( !this.educationalPlan.Equals( value ) )
                    modifiedDBFields.Add( "educational_plan_id" );
                this.educationalPlan = value;
            }
        }

        /// <summary>
        /// Checks if the current instance is valid to be stored in the database.
        /// </summary>
        public override bool IsValid {
            get {
                return !( this.name == null ) && ( this.name.Trim( ) != "" )
                    && ( !( this.abbreviation == null ) && ( this.abbreviation.Trim( ) != "" ) )
                    && ( !( this.code == null ) && ( this.code.Trim( ) != "" ) )
                    && this.credits > 0
                    && this.termNumber > 0
                    && this.educationalPlan.IsValid
                    && this.passingGrade.IsValid;
            }
        }

        /// <summary>
        /// List of prerequisite subjects.
        /// </summary>
        public List<Subject> PreRequisites {
            get {
                List<Subject> reqs = new List<Subject>( );
                Database db = DatabaseFactory.CreateDatabase( );
                DbCommand dbCommand;
                string sqlCommand = "SELECT"
                  + " s.id, s.name, s.abbreviation, s.term_number, s.description, s.credits, s.code, s.required,"
                  + " s.passing_grade_id, g.name AS name_g, g.abbreviation AS abbreviation_g, g.numerical_value AS numerical_value_g, g.passing_grade AS passing_grade_g, g.ordering AS ordering_g,"
                  + " s.educational_plan_id, ep.name AS name_ep, ep.abbreviation AS abbreviation_ep, ep.total_credits AS total_credits_ep, ep.max_duration AS max_duration_ep,"
                  + " ep.duration_in_terms AS duration_in_terms_ep, ep.year AS year_ep, ep.active AS active_ep,"
                  + " ep.term_id, t.name AS name_t, t.duration AS duration_t, t.time_unit_id, tu.name AS name_tu, tu.abbreviation AS abbreviation_tu,"
                  + " ep.academic_degree_id, ad.name AS name_ad, ad.abbreviation AS abbreviation_ad, ad.ordering AS ordering_ad,"
                  + " ep.educational_level_id, el.name AS name_el, el.ordering AS ordering_el"
                  + " FROM educational_plans AS ep, terms AS t, time_units AS tu, educational_levels AS el,"
                  + " grades AS g, subjects AS s, serial_subjects AS ss"
                  + " WHERE s.educational_plan_id = ep.id"
                  + " AND t.id = ep.term_id AND t.time_unit_id = tu.id"
                  + " AND el.id = ep.educational_level_id"
                  + " AND g.id = s.passing_grade_id"
                  + " AND ss.requisite_id = s.id"
                  + " AND ss.subject_id = @id"
                  + " ORDER BY s.name";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32 );
                using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                    Grade g;
                    TimeUnit tu;
                    Term t;
                    AcademicDegree ad;
                    EducationalPlan ep;
                    EducationalLevel el;
                    Subject s;
                    while ( rdr.Read( ) ) {
                        g = new Grade( ( int ) rdr [ "passing_grade_id" ], ( string ) rdr [ "name_g" ], ( string ) rdr [ "abbreviation_g" ],
                            ( decimal ) rdr [ "numerical_value_g" ], ( bool ) rdr [ "passing_grade_g" ], ( int ) rdr [ "ordering_g" ] );
                        tu = new TimeUnit( ( int ) rdr [ "time_unit_id" ], ( string ) rdr [ "name_tu" ], ( string ) rdr [ "abbreviation_tu" ] );
                        t = new Term( ( int ) rdr [ "term_id" ], ( string ) rdr [ "name_t" ], ( int ) rdr [ "duration_t" ], tu );
                        ad = new AcademicDegree( ( int ) rdr [ "academic_degree_id" ], ( string ) rdr [ "name_ad" ],
                            ( string ) rdr [ "abbreviation_ad" ], ( int ) rdr [ "ordering_ad" ] );
                        el = new EducationalLevel( ( int ) rdr [ "educational_level_id" ], ( string ) rdr [ "name_el" ], ( int ) rdr [ "ordering_el" ] );
                        ep = new EducationalPlan( ( int ) rdr [ "educational_plan_id" ], ( string ) rdr [ "name_ep" ], ( string ) rdr [ "abbreviation_ep" ],
                           ( int ) rdr [ "total_credits_ep" ], ( int ) rdr [ "duration_in_terms_ep" ], ( int ) rdr [ "min_duration_ep" ],
                           ( int ) rdr [ "max_duration_ep" ], ( int ) rdr [ "year_ep" ], t, el, ad, ( bool ) rdr [ "active_ep" ] );
                        s = new Subject( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ], ( string ) rdr [ "code" ], ( int ) rdr [ "term_number" ],
                            ( int ) rdr [ "credits" ], ( string ) rdr [ "description" ], ( bool ) rdr [ "required" ], g, ep );
                        reqs.Add( s );
                    }
                    rdr.Close( );
                }
                return reqs;
            }
        }

        /// <summary>
        /// <code>True</code> if this subject has prerequisites, <code>false</code> otherwise.
        /// </summary>
        public bool HasPreRequisites {
            get {
                bool hasPreReqs = false;
                Database db = DatabaseFactory.CreateDatabase( );
                DbCommand dbCommand;
                string sqlCommand = "SELECT (count(*) > 0 ) AS has_pre_reqs FROM serial_subjects"
                  + " WHERE subject_id = @id";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32 );
                hasPreReqs = ( bool ) db.ExecuteScalar( dbCommand );
                return hasPreReqs;
            }
        }
        #endregion

        #region Constructors
        public Subject ( ) {
            this.id = -1;
            this.name = "";
            this.abbreviation = "";
            this.termNumber = 0;
            this.description = "";
            this.credits = 0;
            this.code = "";
            this.passingGrade = new Grade( );
            this.educationalPlan = new EducationalPlan( );
            this.required = false;
            this.isNew = true;
        }

        public Subject ( int id, string name, string abbreviation, string code, int termNumber, int credits, string description,
            bool required, Grade passingGrade, EducationalPlan educationalPlan ) {
            this.id = id;
            this.name = name;
            this.abbreviation = abbreviation;
            this.termNumber = termNumber;
            this.description = description;
            this.credits = credits;
            this.code = code;
            this.passingGrade = passingGrade;
            this.educationalPlan = educationalPlan;
            this.required = required;
            this.isNew = ( id == -1 );
        }

        public Subject ( string name, string abbreviation, string code, int termNumber, int credits, string description,
            bool required, Grade passingGrade, EducationalPlan educationalPlan )
            : this( -1, name, abbreviation, code, termNumber, credits, description, required, passingGrade, educationalPlan ) {
        }
        #endregion

        #region Methods

        #region System.Object Methods
        /// <summary>
        /// Checks if the object is equal to the current instance.
        /// </summary>
        /// <param name="obj">The object to check for equality</param>
        /// <returns><code>True</code> if the objects are equal, <code>false</code> otherwise.</returns>
        public override bool Equals ( object obj ) {
            if ( obj == null ) {
                return false;
            }
            Subject s = obj as Subject;
            if ( ( object ) s == null ) {
                return false;
            }
            return ( this.name == s.name )
                && ( this.abbreviation == s.abbreviation )
                && ( this.required == s.required )
                && ( this.description == s.description )
                && ( this.termNumber == s.termNumber )
                && ( this.credits == s.credits )
                && ( this.code == s.code )
                && this.educationalPlan.Equals( s.educationalPlan )
                && this.passingGrade.Equals( s.passingGrade );
        }

        /// <summary>
        /// Checks if the Subject is equal to the current instance.
        /// </summary>
        /// <param name="a">The Subject to check for equality</param>
        /// <returns><code>True</code> if the objects are equal, <code>false</code> otherwise.</returns>
        public bool Equals ( Subject s ) {
            if ( ( object ) s == null ) {
                return false;
            }
            return ( this.name == s.name )
                && ( this.abbreviation == s.abbreviation )
                && ( this.required == s.required )
                && ( this.description == s.description )
                && ( this.termNumber == s.termNumber )
                && ( this.credits == s.credits )
                && ( this.code == s.code )
                && this.educationalPlan.Equals( s.educationalPlan )
                && this.passingGrade.Equals( s.passingGrade );
        }

        public override int GetHashCode ( ) {
            return id.GetHashCode( )
                ^ this.name.GetHashCode( )
                ^ this.abbreviation.GetHashCode( )
                ^ this.termNumber.GetHashCode( )
                ^ this.description.GetHashCode( )
                ^ this.code.GetHashCode( )
                ^ this.credits.GetHashCode( )
                ^ this.passingGrade.GetHashCode( )
                ^ this.educationalPlan.GetHashCode( )
                ^ this.required.GetHashCode( );
        }

        /// <summary>
        /// String representation of the current instance.
        /// </summary>
        /// <returns>A string representation of the current instance.</returns>
        public override string ToString ( ) {
            StringBuilder bldr = new StringBuilder( );
            object [ ] objArr = new object [ ] { this.name, this.code, this.credits, this.required };
            bldr.AppendFormat( "[{1}] {0}. Credits: {2}. Required: {3:Yes|No}", objArr );
            return bldr.ToString( );
        }
        #endregion

        /// <summary>
        /// Saves the current instance to the database.
        /// </summary>
        /// <returns><code>True</code> if the object was saved to the database, <code>false</code> otherwise.</returns>
        public override bool Save ( ) {
            bool saved = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;

            try {
                sqlCommand = "SELECT count(*) = 0 as exists FROM subjects WHERE id = @id OR name ILIKE @name";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                isNew = ( bool ) db.ExecuteScalar( dbCommand );
                if ( isNew ) {
                    sqlCommand = "SELECT nextval('subjects_id_seq')";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    this.id = ( int ) db.ExecuteScalar( dbCommand );

                    sqlCommand = "INSERT INTO subjects"
                        + " ( id, name, abbreviation, term_number, description, credits, code, required,"
                        + " passing_grade_id, educational_plan_id )"
                        + " VALUES"
                        + " ( @id, @name, @abbreviation, @term_number, @description, @credits,"
                        + " @code, @required, @passing_grade_id, @educational_plan_id )";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                    db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                    db.AddInParameter( dbCommand, "@abbreviation", DbType.String, this.abbreviation );
                    db.AddInParameter( dbCommand, "@term_number", DbType.Int32, this.termNumber );
                    db.AddInParameter( dbCommand, "@description", DbType.String, this.description );
                    db.AddInParameter( dbCommand, "@credits", DbType.Int32, this.credits );
                    db.AddInParameter( dbCommand, "@code", DbType.String, this.code );
                    db.AddInParameter( dbCommand, "@passing_grade_id", DbType.Int32, this.passingGrade.Id );
                    db.AddInParameter( dbCommand, "@educational_plan_id", DbType.Int32, this.educationalPlan.Id );
                    db.AddInParameter( dbCommand, "@required", DbType.Boolean, this.required );
                    saved = db.ExecuteNonQuery( dbCommand ) > 0;
                }
                else if ( this.modifiedDBFields.Count > 0 ) {
                    sqlCommand = "UPDATE subjects SET"
                        + " id = @id, name = @name, abbreviation = @abbreviation, term_number = @term_number,"
                        + " description = @description, credits = @credits,"
                        + " code = @code, required = @required, passing_grade_id = @passing_grade_id,"
                        + " educational_plan_id = @educational_plan_id"
                        + " WHERE id = @id";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                    db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                    db.AddInParameter( dbCommand, "@abbreviation", DbType.String, this.abbreviation );
                    db.AddInParameter( dbCommand, "@term_number", DbType.Int32, this.termNumber );
                    db.AddInParameter( dbCommand, "@description", DbType.String, this.description );
                    db.AddInParameter( dbCommand, "@credits", DbType.Int32, this.credits );
                    db.AddInParameter( dbCommand, "@code", DbType.String, this.code );
                    db.AddInParameter( dbCommand, "@passing_grade_id", DbType.Int32, this.passingGrade.Id );
                    db.AddInParameter( dbCommand, "@educational_plan_id", DbType.Int32, this.educationalPlan.Id );
                    db.AddInParameter( dbCommand, "@required", DbType.Boolean, this.required );
                    saved = db.ExecuteNonQuery( dbCommand ) > 0;
                }
                else {
                    //No update or insert needed
                    saved = true;
                }
            }
            catch ( Exception e ) {
                saved = false;
                this.errorMessages.Add( "There was an error saving the Subject: " + e.Message );
            }
            return saved;
        }

        /// <summary>
        /// Deletes the current object from the database.
        /// </summary>
        /// <returns><code>True</code> if the object was deleted from the database, <code>false</code> otherwise.</returns>
        public override bool Delete ( ) {
            bool deleted = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;
            try {
                sqlCommand = "DELETE FROM subjects WHERE id = @id";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                deleted = db.ExecuteNonQuery( dbCommand ) >= 0;
            }
            catch ( Exception e ) {
                deleted = false;
                this.errorMessages.Add( "There was an error saving the Subject: " + e.Message );
            }
            return deleted;
        }

        /// <summary>
        /// Checks if the Subject <code>s</code> is prerequisite of <code>this</code> subject.
        /// </summary>
        /// <param name="s">A Subject</param>
        /// <returns><code>True</code> if <code>s</code> is prerequisite of <code>this</code>, <code>false</code> otherwise.</returns>
        public bool IsPreRequisite ( Subject s ) {
            bool isPreReq = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT count(*) > 0 FROM serial_subjects WHERE subject_id = @id AND requisite_id = @requisite_id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@subject_id", DbType.Int32, this.id );
            db.AddInParameter( dbCommand, "@requisite_id", DbType.Int32, s.id );
            isPreReq = ( bool ) db.ExecuteScalar( dbCommand );
            return isPreReq;
        }

        /// <summary>
        /// Saves a subject as a prerequisite of <code>this</code>.
        /// </summary>
        /// <param name="preRequisite">The subject prerequisite.</param>
        ///  <returns><code>True</code> if <code>preRequisite</code> is saved as a prerequisite of <code>this</code>, <code>false</code> otherwise.</returns>
        public bool SavePreRequisite ( Subject preRequisite ) {
            bool saved = false;
            bool isPreReq = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;

            try {
                sqlCommand = "SELECT count(*) > 0 FROM serial_subjects WHERE subject_id = @id AND requisite_id = @requisite_id";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@subject_id", DbType.Int32, this.id );
                db.AddInParameter( dbCommand, "@requisite_id", DbType.Int32, preRequisite.id );
                isPreReq = ( bool ) db.ExecuteScalar( dbCommand );

                if ( isPreReq ) {
                    saved = true;
                }
                else {
                    sqlCommand = "INSERT INTO serial_subjects (subject_id, requisite_id) VALUES (@subject_id, @requisite_id)";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    db.AddInParameter( dbCommand, "@subject_id", DbType.Int32, this.id );
                    db.AddInParameter( dbCommand, "@requisite_id", DbType.Int32, preRequisite.id );
                    saved = db.ExecuteNonQuery( dbCommand ) > 0;
                }
            }
            catch ( Exception e ) {
                saved = false;
                this.errorMessages.Add( "There was an error saving the Subject prerequsite: " + e.Message );
            }
            return saved;
        }

        /// <summary>
        /// Saves a list of subjects as prerequisites of <code>this</code>.
        /// </summary>
        /// <param name="preRequisites">A list of prerequisite subjects.</param>
        /// <returns>The number of saved as prerequisite subjects from the list.</returns>
        public int SavePreRequisites ( List<Subject> preRequisites ) {
            int saved = 0;
            bool isPreReq = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;


            foreach ( Subject preRequisite in preRequisites ) {
                try {
                    sqlCommand = "SELECT count(*) > 0 FROM serial_subjects WHERE subject_id = @id AND requisite_id = @requisite_id";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    db.AddInParameter( dbCommand, "@subject_id", DbType.Int32, this.id );
                    db.AddInParameter( dbCommand, "@requisite_id", DbType.Int32, preRequisite.id );
                    isPreReq = ( bool ) db.ExecuteScalar( dbCommand );

                    if ( isPreReq ) {
                        saved++;
                    }
                    else {
                        sqlCommand = "INSERT INTO serial_subjects (subject_id, requisite_id) VALUES (@subject_id, @requisite_id)";
                        dbCommand = db.GetSqlStringCommand( sqlCommand );
                        db.AddInParameter( dbCommand, "@subject_id", DbType.Int32, this.id );
                        db.AddInParameter( dbCommand, "@requisite_id", DbType.Int32, preRequisite.id );
                        if ( db.ExecuteNonQuery( dbCommand ) > 0 )
                            saved++;
                    }
                }
                catch ( Exception e ) {
                    this.errorMessages.Add( "There was an error saving the Subject prerequsite: " + e.Message );
                }
            }
            return saved;
        }

        /// <summary>
        /// Checks for equivalence between two subjects.
        /// </summary>
        /// <param name="a">The first Subject</param>
        /// <param name="b">The second Subject</param>
        /// <returns><code>True</code> if <code>a</code> is equivalent to <code>b</code>, <code>false</code> otherwise.</returns>
        public static bool AreEquivalent ( Subject a, Subject b ) {
            bool areEquivalent = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT count(*) > 0 FROM equivalent_subjects"
                + " WHERE (subject_id = @aid AND equivalent_to_id = @bid)"
                + " OR (subject_id = @bid AND equivalent_to_id = @aid)";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@aid", DbType.Int32, a.id );
            db.AddInParameter( dbCommand, "@bid", DbType.Int32, b.id );
            areEquivalent = ( bool ) db.ExecuteScalar( dbCommand );
            return areEquivalent;
        }

        /// <summary>
        /// Checks for equivalence between <code>this</code> and another subject.
        /// </summary>
        /// <param name="s">A Subject</param>
        /// <returns><code>True</code> if <code>this</code> is equivalent to <code>s</code>, <code>false</code> otherwise.</returns>
        public bool IsEquivalentTo ( Subject s ) {
            bool areEquivalent = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT count(*) > 0 FROM equivalent_subjects"
                + " WHERE (subject_id = @aid AND equivalent_to_id = @bid)";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@aid", DbType.Int32, s.id );
            db.AddInParameter( dbCommand, "@bid", DbType.Int32, this.id );
            areEquivalent = ( bool ) db.ExecuteScalar( dbCommand );
            return areEquivalent;
        }

        /// <summary>
        /// Saves into the database the equivalence between the two Subjects.
        /// </summary>
        /// <param name="a">First Subject</param>
        /// <param name="b">Second Subject</param>
        /// <returns><code>True</code> if the Subjects' equivalence was saved successfully, <code>false</code> otherwise.</returns>
        public static bool SaveEquivalent ( Subject a, Subject b ) {
            bool saved = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand, dbCommandAux;
            string sqlCommand;
            if ( a.IsValid && b.IsValid ) {
                if ( AreEquivalent( a, b ) ) {
                    saved = true;
                }
                else {
                    try {
                        sqlCommand = "INSERT INTO equivalent_subjects ( subject_id, equivalent_to_id)"
                            + " VALUES ( @id, @eid )";
                        dbCommand = db.GetSqlStringCommand( sqlCommand );
                        db.AddInParameter( dbCommand, "@id", DbType.Int32, a.id );
                        db.AddInParameter( dbCommand, "@eid", DbType.Int32, b.id );

                        dbCommandAux = db.GetSqlStringCommand( sqlCommand );
                        db.AddInParameter( dbCommandAux, "@id", DbType.Int32, b.id );
                        db.AddInParameter( dbCommandAux, "@eid", DbType.Int32, a.id );

                        using ( DbConnection connection = db.CreateConnection( ) ) {
                            connection.Open( );
                            DbTransaction transaction = connection.BeginTransaction( );
                            try {
                                db.ExecuteNonQuery( dbCommand, transaction );
                                db.ExecuteNonQuery( dbCommandAux, transaction );
                                transaction.Commit( );
                                saved = true;
                            }
                            catch {
                                transaction.Rollback( );
                                saved = false;
                            }
                        }
                    }
                    catch {
                        saved = false;
                    }
                }
            }
            return saved;
        }

        /// <summary>
        /// Saves into the database the equivalence of <code>this</code> to another subject.
        /// </summary>
        /// <param name="a">First Subject</param>
        /// <param name="b">Second Subject</param>
        /// <returns><code>True</code> if the Subjects' equivalence was saved successfully, <code>false</code> otherwise.</returns>
        public bool SaveEquivalence ( Subject s ) {
            bool saved = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;
            if ( this.IsValid && s.IsValid ) {
                if ( this.IsEquivalentTo( s ) ) {
                    saved = true;
                }
                else {
                    try {
                        sqlCommand = "INSERT INTO equivalent_subjects ( subject_id, equivalent_to_id)"
                            + " VALUES ( @id, @eid )";
                        dbCommand = db.GetSqlStringCommand( sqlCommand );
                        db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                        db.AddInParameter( dbCommand, "@eid", DbType.Int32, s.id );
                        saved = db.ExecuteNonQuery( dbCommand ) > 0;
                    }
                    catch ( Exception e ) {
                        saved = false;
                        this.errorMessages.Add( "There was an error saving the Subject equivalence: " + e.Message );
                    }
                }
            }
            return saved;
        }

        #region Load Methods
        /// <summary>
        /// Loads the Subject with the given <code>id</code>.
        /// </summary>
        /// <param name="id">The Subjects' id.</param>
        /// <returns>A Subject object with it's information or a null Subject object if the <code>id</code> was not present in the database.</returns>
        public static Subject Load ( int id ) {
            Subject s = null;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            //TODO: Crear vista
            string sqlCommand = "SELECT"
                + " s.id, s.name, s.abbreviation, s.term_number, s.description, s.credits, s.code, s.required,"
                + " s.passing_grade_id, g.name AS name_g, g.abbreviation AS abbreviation_g, g.numerical_value AS numerical_value_g, g.passing_grade AS passing_grade_g, g.ordering AS ordering_g,"
                + " s.educational_plan_id, ep.name AS name_ep, ep.abbreviation AS abbreviation_ep, ep.total_credits AS total_credits_ep, ep.max_duration AS max_duration_ep,"
                + " ep.duration_in_terms AS duration_in_terms_ep, ep.year AS year_ep, ep.active AS active_ep,"
                + " ep.term_id, t.name AS name_t, t.duration AS duration_t, t.time_unit_id, tu.name AS name_tu, tu.abbreviation AS abbreviation_tu,"
                + " ep.academic_degree_id, ad.name AS name_ad, ad.abbreviation AS abbreviation_ad, ad.ordering AS ordering_ad,"
                + " ep.educational_level_id, el.name AS name_el, el.ordering AS ordering_el"
                + " FROM educational_plans AS ep, terms AS t, time_units AS tu, educational_levels AS el,"
                + " grades AS g, subjects AS s"
                + " WHERE s.id = @id"
                + " AND s.educational_plan_id = ep.id"
                + " AND t.id = ep.term_id AND t.time_unit_id = tu.id"
                + " AND el.id = ep.educational_level_id"
                + " AND g.id = s.passing_grade_id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32, id );
            try {
                using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                    if ( rdr.Read( ) ) {
                        Grade g = new Grade( ( int ) rdr [ "passing_grade_id" ], ( string ) rdr [ "name_g" ], ( string ) rdr [ "abbreviation_g" ],
                            ( decimal ) rdr [ "numerical_value_g" ], ( bool ) rdr [ "passing_grade_g" ], ( int ) rdr [ "ordering_g" ] );
                        TimeUnit tu = new TimeUnit( ( int ) rdr [ "time_unit_id" ], ( string ) rdr [ "name_tu" ], ( string ) rdr [ "abbreviation_tu" ] );
                        Term t = new Term( ( int ) rdr [ "term_id" ], ( string ) rdr [ "name_t" ], ( int ) rdr [ "duration_t" ], tu );
                        AcademicDegree ad = new AcademicDegree( ( int ) rdr [ "academic_degree_id" ], ( string ) rdr [ "name_ad" ],
                            ( string ) rdr [ "abbreviation_ad" ], ( int ) rdr [ "ordering_ad" ] );
                        EducationalLevel el = new EducationalLevel( ( int ) rdr [ "educational_level_id" ], ( string ) rdr [ "name_el" ], ( int ) rdr [ "ordering_el" ] );
                        EducationalPlan ep = new EducationalPlan( ( int ) rdr [ "educational_plan_id" ], ( string ) rdr [ "name_ep" ], ( string ) rdr [ "abbreviation_ep" ],
                            ( int ) rdr [ "total_credits_ep" ], ( int ) rdr [ "duration_in_terms_ep" ], ( int ) rdr [ "min_duration_ep" ],
                            ( int ) rdr [ "max_duration_ep" ], ( int ) rdr [ "year_ep" ], t, el, ad, ( bool ) rdr [ "active_ep" ] );
                        s = new Subject( id, ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ], ( string ) rdr [ "code" ], ( int ) rdr [ "term_number" ],
                            ( int ) rdr [ "credits" ], ( string ) rdr [ "description" ], ( bool ) rdr [ "required" ], g, ep );
                        s.modifiedDBFields.Clear( );
                        s.ClearMessages( );
                        s.isNew = false;
                    }
                    rdr.Close( );
                }
            }
            catch ( Exception e ) {
                s.errorMessages.Add( string.Format( "There was an error loading the Subject with ID {0}: {1}", id.ToString( ), e.Message ) );
            }
            return s;
        }

        /// <summary>
        /// Loads the Subject with the given <code>name</code>.
        /// </summary>
        /// <param name="id">The Subjects' name.</param>
        /// <returns>A Subject object with it's information or a null Subject object if the <code>name</code> was not present in the database.</returns>
        public static Subject LoadByName ( string name ) {
            Subject s = null;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT"
                + " s.id, s.name, s.abbreviation, s.term_number, s.description, s.credits, s.code, s.required,"
                + " s.passing_grade_id, g.name AS name_g, g.abbreviation AS abbreviation_g, g.numerical_value AS numerical_value_g, g.passing_grade AS passing_grade_g, g.ordering AS ordering_g,"
                + " s.educational_plan_id, ep.name AS name_ep, ep.abbreviation AS abbreviation_ep, ep.total_credits AS total_credits_ep, ep.max_duration AS max_duration_ep,"
                + " ep.duration_in_terms AS duration_in_terms_ep, ep.year AS year_ep, ep.active AS active_ep,"
                + " ep.term_id, t.name AS name_t, t.duration AS duration_t, t.time_unit_id, tu.name AS name_tu, tu.abbreviation AS abbreviation_tu,"
                + " ep.academic_degree_id, ad.name AS name_ad, ad.abbreviation AS abbreviation_ad, ad.ordering AS ordering_ad,"
                + " ep.educational_level_id, el.name AS name_el, el.ordering AS ordering_el"
                + " FROM educational_plans AS ep, terms AS t, time_units AS tu, educational_levels AS el,"
                + " grades AS g, subjects AS s"
                + " WHERE s.name ILIKE @name"
                + " AND s.educational_plan_id = ep.id"
                + " AND t.id = ep.term_id AND t.time_unit_id = tu.id"
                + " AND el.id = ep.educational_level_id"
                + " AND g.id = s.passing_grade_id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@name", DbType.String, name );
            try {
                using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                    if ( rdr.Read( ) ) {
                        Grade g = new Grade( ( int ) rdr [ "passing_grade_id" ], ( string ) rdr [ "name_g" ], ( string ) rdr [ "abbreviation_g" ],
                         ( decimal ) rdr [ "numerical_value_g" ], ( bool ) rdr [ "passing_grade_g" ], ( int ) rdr [ "ordering_g" ] );
                        TimeUnit tu = new TimeUnit( ( int ) rdr [ "time_unit_id" ], ( string ) rdr [ "name_tu" ], ( string ) rdr [ "abbreviation_tu" ] );
                        Term t = new Term( ( int ) rdr [ "term_id" ], ( string ) rdr [ "name_t" ], ( int ) rdr [ "duration_t" ], tu );
                        AcademicDegree ad = new AcademicDegree( ( int ) rdr [ "academic_degree_id" ], ( string ) rdr [ "name_ad" ],
                            ( string ) rdr [ "abbreviation_ad" ], ( int ) rdr [ "ordering_ad" ] );
                        EducationalLevel el = new EducationalLevel( ( int ) rdr [ "educational_level_id" ], ( string ) rdr [ "name_el" ], ( int ) rdr [ "ordering_el" ] );
                        EducationalPlan ep = new EducationalPlan( ( int ) rdr [ "educational_plan_id" ], ( string ) rdr [ "name_ep" ], ( string ) rdr [ "abbreviation_ep" ],
                            ( int ) rdr [ "total_credits_ep" ], ( int ) rdr [ "duration_in_terms_ep" ], ( int ) rdr [ "min_duration_ep" ],
                            ( int ) rdr [ "max_duration_ep" ], ( int ) rdr [ "year_ep" ], t, el, ad, ( bool ) rdr [ "active_ep" ] );
                        s = new Subject( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ], ( string ) rdr [ "code" ], ( int ) rdr [ "term_number" ],
                            ( int ) rdr [ "credits" ], ( string ) rdr [ "description" ], ( bool ) rdr [ "required" ], g, ep );
                        s.modifiedDBFields.Clear( );
                        s.ClearMessages( );
                        s.isNew = false;
                    }
                    rdr.Close( );
                }
            }
            catch ( Exception e ) {
                s.errorMessages.Add( string.Format( "There was an error loading the Subject {0}: {1}", name, e.Message ) );
            }
            return s;
        }

        /// <summary>
        /// Checks the database for existence of the Subject with the given <code>id</code>.
        /// </summary>
        /// <param name="id">The Subjects' id.</param>
        /// <returns><code>True</code> if the grade with the given <code>id</code>exists, <code>false</code> otherwise.</returns>
        public static bool Exists ( int id ) {
            bool exists = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT count(*) > 0 as exists FROM subjects WHERE id = @id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32, id );
            exists = ( bool ) db.ExecuteScalar( dbCommand );
            return exists;
        }

        /// <summary>
        /// Checks the database for existence of the Subject with the given <code>name</code>.
        /// </summary>
        /// <param name="name">The Subjects' name</param>
        /// <returns><code>True</code> if the grade with the given <code>name</code>exists, <code>false</code> otherwise.</returns>
        public static bool ExistsByName ( string name ) {
            bool exists = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT count(*) > 0 as exists FROM subjects WHERE name ILIKE @name";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@name", DbType.String, name );
            exists = ( bool ) db.ExecuteScalar( dbCommand );
            return exists;
        }

        /// <summary>
        /// Loads all of the Subjects in the database to a list.
        /// </summary>
        /// <returns>A List containing all of the Subjects on the database.</returns>
        public static List<Subject> LoadAll ( ) {
            List<Subject> sl = new List<Subject>( );
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT"
              + " s.id, s.name, s.abbreviation, s.term_number, s.description, s.credits, s.code, s.required,"
              + " s.passing_grade_id, g.name AS name_g, g.abbreviation AS abbreviation_g, g.numerical_value AS numerical_value_g, g.passing_grade AS passing_grade_g, g.ordering AS ordering_g,"
              + " s.educational_plan_id, ep.name AS name_ep, ep.abbreviation AS abbreviation_ep, ep.total_credits AS total_credits_ep, ep.max_duration AS max_duration_ep,"
              + " ep.duration_in_terms AS duration_in_terms_ep, ep.year AS year_ep, ep.active AS active_ep,"
              + " ep.term_id, t.name AS name_t, t.duration AS duration_t, t.time_unit_id, tu.name AS name_tu, tu.abbreviation AS abbreviation_tu,"
              + " ep.academic_degree_id, ad.name AS name_ad, ad.abbreviation AS abbreviation_ad, ad.ordering AS ordering_ad,"
              + " ep.educational_level_id, el.name AS name_el, el.ordering AS ordering_el"
              + " FROM educational_plans AS ep, terms AS t, time_units AS tu, educational_levels AS el,"
              + " grades AS g, subjects AS s"
              + " WHERE s.educational_plan_id = ep.id"
              + " AND t.id = ep.term_id AND t.time_unit_id = tu.id"
              + " AND el.id = ep.educational_level_id"
              + " AND g.id = s.passing_grade_id"
              + " ORDER BY ep.name, s.name";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                Grade g;
                TimeUnit tu;
                Term t;
                AcademicDegree ad;
                EducationalPlan ep;
                EducationalLevel el;
                Subject s;
                while ( rdr.Read( ) ) {
                    g = new Grade( ( int ) rdr [ "passing_grade_id" ], ( string ) rdr [ "name_g" ], ( string ) rdr [ "abbreviation_g" ],
                        ( decimal ) rdr [ "numerical_value_g" ], ( bool ) rdr [ "passing_grade_g" ], ( int ) rdr [ "ordering_g" ] );
                    tu = new TimeUnit( ( int ) rdr [ "time_unit_id" ], ( string ) rdr [ "name_tu" ], ( string ) rdr [ "abbreviation_tu" ] );
                    t = new Term( ( int ) rdr [ "term_id" ], ( string ) rdr [ "name_t" ], ( int ) rdr [ "duration_t" ], tu );
                    ad = new AcademicDegree( ( int ) rdr [ "academic_degree_id" ], ( string ) rdr [ "name_ad" ],
                        ( string ) rdr [ "abbreviation_ad" ], ( int ) rdr [ "ordering_ad" ] );
                    el = new EducationalLevel( ( int ) rdr [ "educational_level_id" ], ( string ) rdr [ "name_el" ], ( int ) rdr [ "ordering_el" ] );
                    ep = new EducationalPlan( ( int ) rdr [ "educational_plan_id" ], ( string ) rdr [ "name_ep" ], ( string ) rdr [ "abbreviation_ep" ],
                       ( int ) rdr [ "total_credits_ep" ], ( int ) rdr [ "duration_in_terms_ep" ], ( int ) rdr [ "min_duration_ep" ],
                       ( int ) rdr [ "max_duration_ep" ], ( int ) rdr [ "year_ep" ], t, el, ad, ( bool ) rdr [ "active_ep" ] );
                    s = new Subject( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ], ( string ) rdr [ "code" ], ( int ) rdr [ "term_number" ],
                        ( int ) rdr [ "credits" ], ( string ) rdr [ "description" ], ( bool ) rdr [ "required" ], g, ep );
                    sl.Add( s );
                }
                rdr.Close( );
            }
            return sl;
        }
        #endregion

        #endregion
    }
}
